#!/usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import sys
import cgi
import json
import db_conf
import metabobase_util

con = None

try:
    qform = cgi.FieldStorage()
    search_input = qform.getvalue('search_input')
    rowlimit = qform.getvalue('limit') 
    config = db_conf.getConfig('metabvis.conf')
    con = psycopg2.connect(host=db_conf.getDBHost(config), database=db_conf.getDBName(config), port=db_conf.getDBPort(config), user=db_conf.getUser(config), password=db_conf.getPassword(config)) 
    cur = con.cursor()
    #default select if no filtering is set
    isprecursor = False	
    compound_fields="c.compound_id, compound_name, c.time_lastmod, sum_formula,round(cast(nominal_mass as numeric),0),round(cast(monoisotopic_mass as numeric),4),round(cast(molecular_weight as numeric),2),reference_spectrum_id,ref_cas_id,ref_pubchem_id,ref_hmdb_id,ref_nist_id,ref_chemspider_id,checked,c.user_comment,inchi,inchi_key,precursor_compound,derivatization_reagent_id,ref_kegg_id,struc.compound_id"
    order_by = " order by c.time_lastmod DESC LIMIT " + rowlimit	
    source_tables = "compound c LEFT JOIN compound_derivatization cd ON (c.compound_id = cd.product_compound) LEFT JOIN compound_spectrum cs ON (c.compound_id = cs.compound_id) LEFT JOIN compound_structure struc on (c.compound_id = struc.compound_id) LEFT JOIN spectrum sp on (sp.spectrum_id = cs.spectrum_id)"
    #source_tables = "compound c LEFT JOIN compound_derivatization cd ON (c.compound_id = cd.product_compound) LEFT JOIN spectrum_origin_view sv ON (c.compound_id = sv.compound_id) LEFT JOIN compound_structure struc on (c.compound_id = struc.compound_id)"
    if (search_input == None or search_input == ""):
        source_tables = "compound c LEFT JOIN compound_derivatization cd ON (c.compound_id = cd.product_compound) LEFT JOIN compound_structure struc on (c.compound_id = struc.compound_id)"		
    	select_experiments = "select " + compound_fields + " from " + source_tables + " where c.archived = 0 " + order_by
    elif (metabobase_util.isInt(search_input)):
        q = "c.compound_id = " + search_input
        select_experiments = "select " + compound_fields + " from " + source_tables + " where " + q + " " + order_by
    elif (search_input.find(":comment") != -1):
        stk = search_input.split(":")
        sourcetable = stk[0]
        scomment = "!= ''"
        if (len(stk) == 3):
            scomment = "like '%" + stk[2] + "%'"
        q = "c.user_comment " + scomment #!= ''"
        if (sourcetable.upper().find("COMPOUND") != -1):	
            q = "c.user_comment " + scomment #!= ''"
        elif (sourcetable.upper().find("SPECTRUM") != -1):
            q = "sp.user_comment " + scomment #!= ''"
        select_experiments = "select " + compound_fields + " from " + source_tables + " where " + q + " " + order_by
    elif (search_input[0:3] == "mz:" or search_input[0:3] == "mz=" or search_input[0:3] == "mz<" or search_input[0:3] == "mz>"):
        source_tables = "compound c LEFT JOIN compound_derivatization cd ON (c.compound_id = cd.product_compound) LEFT JOIN compound_spectrum cs ON (c.compound_id = cs.compound_id) LEFT JOIN compound_structure struc on (c.compound_id = struc.compound_id) LEFT JOIN spectrum sp ON (cs.spectrum_id = sp.spectrum_id) LEFT JOIN spectral_peak spp ON (sp.spectrum_id = spp.spectrum_id)"       
        search_input = search_input.replace("mz:", "mz=")
        if (search_input.find("ri") != -1):            
            q = search_input.replace("ri", "relative_intensity")
            if (len(q.split(" ")) == 2):
                qtk = q.split(" ")
                q = qtk[0] + " and " + qtk[1]		  
        else:
            q = search_input + " and relative_intensity >= 0.05"
        select_experiments = "select " + compound_fields + " from " + source_tables + " where " + q + " " + order_by
    else:
        source_tables = "compound c LEFT JOIN compound_derivatization cd ON (c.compound_id = cd.product_compound) LEFT JOIN compound_spectrum cs ON (c.compound_id = cs.compound_id) LEFT JOIN compound_synonyms syn ON (c.compound_id = syn.compound_id) LEFT JOIN compound_structure struc on (c.compound_id = struc.compound_id) LEFT JOIN spectrum sp on (sp.spectrum_id = cs.spectrum_id)"
	q = "LOWER(compound_name) like LOWER('%" + search_input + "%') OR " + "LOWER(synonym_text) like LOWER('%" + search_input + "%')"
	precursor_id = qform.getvalue('precursor_id')
	if (precursor_id != None and len(precursor_id) > 0):
		compound_fields="c.compound_id, compound_name,sum_formula,nominal_mass,monoisotopic_mass,molecular_weight,reference_spectrum_id,ref_cas_id,ref_pubchem_id,ref_hmdb_id,ref_nist_id,ref_chemspider_id,checked,c.user_comment,inchi,inchi_key,precursor_compound,derivatization_reagent_id,ref_kegg_id, cs.spectrum_id, struc.compound_id"
		q = "c.compound_id = " + precursor_id
        	isprecursor = True
	select_experiments = "select " + compound_fields + " from " + source_tables + " where " + q + " " + order_by 
    #print select_experiments
    cur.execute(select_experiments)          
    rows = cur.fetchall()    
    print "Content-type: text/html;charset=utf-8\r\n"
    j = []
    compound_bin = {}
    pa = {}
    pa['aaData'] = []
    for row in rows:
	pi = []
	for r in range(len(row)):
		if (r == 0):
			cid = row[r]
			if (compound_bin.get(cid) == None):
				compound_bin[cid] = cid
			else:
				break
		pi.append(str(row[r]))
		if (r == 2 and isprecursor == False):
			spectrum_origin_sql = "select spectrum_origin_id from spectrum_origin_view where compound_id = " + str(cid) #100;
			cur.execute(spectrum_origin_sql)
			originrow = cur.fetchone()
			if (originrow != None):
			#if (row[len(row)-2] != None and row[len(row)-2] != ""):
				#spectrum_origin = row[len(row)-2]
				#spectrum_origin_sql = "select spectrum_origin_id from spectrum_origin_view where compound_id = " + str(cid) #100;
				#cur.execute(spectrum_origin_sql)
				#originrow = cur.fetchone()
				origin_ct = originrow[0]
				if (origin_ct == 1):
					pi.append("<img src='images/lcsbicon.png'>")
				else:
					pi.append("<img src='images/star.png'>")
				
				#if (spectrum_origin == 1):
				#	pi.append("<img src='images/lcsbicon.png'>")	
				#else:
				#	pi.append("<img src='images/star.png'>")
			else:
				pi.append("")			
	if (pi != []):
		pa['aaData'].append(pi)
    print json.dumps(pa)
except psycopg2.DatabaseError, e:
    print 'Error %s' % e    
    sys.exit(1)    
finally:
    
    if con:
        con.close()
